import pandas as pd
from difflib import SequenceMatcher
import re
from concurrent.futures import ThreadPoolExecutor

def clean_text(text):
    """清洗文本，移除特殊字符和空格"""
    if pd.isna(text):
        return ""
    return re.sub(r'[^\w]', '', str(text).lower().strip())

def find_best_match(main_row, print_df):
    """为单行数据查找最佳匹配"""
    main_manufacturer = main_row['设备厂家_clean']
    main_model = main_row['产品型号_clean']
    
    best_match_score = 0
    best_match_brand = ""
    best_match_model = ""
    
    for _, print_row in print_df.iterrows():
        print_brand = print_row['品牌_clean']
        print_model = print_row['型号_clean']
        
        # 计算型号相似度
        model_similarity = SequenceMatcher(None, main_model, print_model).ratio()
        
        # 计算品牌相似度
        brand_similarity = SequenceMatcher(None, main_manufacturer, print_brand).ratio()
        
        # 综合相似度
        total_similarity = brand_similarity * 0.3 + model_similarity * 0.7
        
        if total_similarity > best_match_score:
            best_match_score = total_similarity
            best_match_brand = print_row['品牌']
            best_match_model = print_row['型号']
    
    return main_row, best_match_score, best_match_brand, best_match_model

def fuzzy_match_and_export_optimized(print_file, main_file, output_file, similarity_threshold=0.7, max_workers=None):
    """
    优化版的模糊匹配，使用多线程处理大型数据集
    
    Args:
        print_file: print.xls 文件路径
        main_file: 1.xlsx 文件路径
        output_file: 输出文件路径
        similarity_threshold: 相似度阈值 (0-1)
        max_workers: 最大线程数
    
    Returns:
        匹配到的行数
    """
    # 读取数据
    print_df = pd.read_excel(print_file, sheet_name='Sheet1')
    main_df = pd.read_excel(main_file, sheet_name='Sheet1')
    
    # 数据清洗
    print_df = print_df.dropna(subset=['品牌', '型号']).copy()
    print_df['品牌_clean'] = print_df['品牌'].apply(clean_text)
    print_df['型号_clean'] = print_df['型号'].apply(clean_text)
    
    main_df = main_df.dropna(subset=['设备厂家', '产品型号']).copy()
    main_df['设备厂家_clean'] = main_df['设备厂家'].apply(clean_text)
    main_df['产品型号_clean'] = main_df['产品型号'].apply(clean_text)
    
    # 使用多线程处理匹配
    matched_rows = []
    
    with ThreadPoolExecutor(max_workers=max_workers) as executor:
        # 提交所有匹配任务
        futures = [
            executor.submit(find_best_match, main_row, print_df)
            for _, main_row in main_df.iterrows()
        ]
        
        # 收集结果
        for future in futures:
            main_row, best_match_score, best_match_brand, best_match_model = future.result()
            
            if best_match_score >= similarity_threshold:
                matched_row = main_row.copy()
                matched_row['匹配相似度'] = round(best_match_score, 3)
                matched_row['匹配到的品牌'] = best_match_brand
                matched_row['匹配到的型号'] = best_match_model
                matched_rows.append(matched_row)
    
    # 导出结果
    if matched_rows:
        result_df = pd.DataFrame(matched_rows)
        
        # 重新排列列的顺序
        original_columns = list(main_df.columns)
        extra_columns = ['匹配相似度', '匹配到的品牌', '匹配到的型号']
        
        # 确保所有列都存在
        for col in extra_columns:
            if col not in result_df.columns:
                result_df[col] = ""
        
        # 重新排列列顺序
        new_column_order = extra_columns + original_columns
        result_df = result_df[new_column_order]
        
        # 导出到Excel
        result_df.to_excel(output_file, index=False, engine='openpyxl')
        
        print(f"成功导出 {len(matched_rows)} 条匹配记录到 {output_file}")
        return len(matched_rows)
    else:
        print("没有找到匹配的设备")
        return 0

# 使用示例
if __name__ == "__main__":
    print_file = "print.xls"
    main_file = "data.xlsx"
    output_file = "匹配结果.xlsx"
    
    try:
        # 使用优化版本（适用于大型数据集）
        match_count = fuzzy_match_and_export_optimized(
            print_file, 
            main_file, 
            output_file,
            similarity_threshold=0.7,
            max_workers=4  # 根据CPU核心数调整
        )
        
        print(f"匹配完成，共找到 {match_count} 条记录")
        
    except Exception as e:
        print(f"处理文件时出错: {e}")